#!/usr/bin/env python3
"""
简单的数据库初始化脚本
"""

import os
import sys
import sqlite3
from pathlib import Path

def create_database():
    """创建数据库和基本表结构"""
    # 确保数据目录存在
    data_dir = Path("data")
    data_dir.mkdir(exist_ok=True)
    
    db_path = data_dir / "app.db"
    print(f"📁 数据库路径: {db_path}")
    
    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # 创建版本表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS alembic_version (
                version_num VARCHAR(32) NOT NULL,
                CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
            )
        ''')
        
        # 插入当前版本
        cursor.execute('''
            INSERT OR REPLACE INTO alembic_version (version_num)
            VALUES ('head')
        ''')
        
        # 创建用户表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id VARCHAR(255) PRIMARY KEY,
                username VARCHAR(255) NOT NULL,
                email VARCHAR(255),
                is_active BOOLEAN DEFAULT TRUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建有声读物表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS audiobooks (
                id VARCHAR(255) PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                author VARCHAR(255),
                description TEXT,
                language VARCHAR(10),
                status VARCHAR(50) DEFAULT 'pending',
                duration FLOAT DEFAULT 0,
                chapters_count INTEGER DEFAULT 0,
                file_path TEXT,
                file_size INTEGER,
                user_id VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建章节表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS audiobook_chapters (
                id VARCHAR(255) PRIMARY KEY,
                audiobook_id VARCHAR(255),
                title VARCHAR(255),
                content TEXT,
                order_number INTEGER,
                duration FLOAT DEFAULT 0,
                file_path TEXT,
                file_size INTEGER,
                status VARCHAR(50) DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (audiobook_id) REFERENCES audiobooks (id)
            )
        ''')
        
        # 创建语音配置表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS voice_profiles (
                id VARCHAR(255) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                language VARCHAR(10),
                gender VARCHAR(10),
                engine VARCHAR(50),
                voice_id VARCHAR(255),
                settings TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 提交事务
        conn.commit()
        print("✅ 数据库初始化成功！")
        
        # 显示表信息
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        tables = cursor.fetchall()
        print(f"📊 创建的表: {[table[0] for table in tables]}")
        
        return True
        
    except Exception as e:
        print(f"❌ 数据库初始化失败: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()

if __name__ == "__main__":
    print("🔧 初始化离线语言播放系统数据库...")
    
    if create_database():
        print("\n🎉 数据库准备完成！")
        print("💡 现在可以启动应用了:")
        print("   uvicorn app.main:app --reload")
    else:
        print("\n❌ 数据库初始化失败！")
        sys.exit(1)
